package cn.cj.erp.common.excel.excel;

import cn.cj.erp.common.enums.BaseEnum;
import cn.cj.erp.common.excel.anno.Excel;
import cn.cj.erp.common.excel.enums.ColumnType;
import cn.cj.erp.common.excel.enums.ExcelType;
import cn.cj.erp.common.excel.format.ExcelFormatter;
import cn.cj.erp.common.exception.ServiceException;
import cn.cj.erp.common.utils.Convert;
import cn.cj.erp.common.utils.file.ImageUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.RegExUtils;
import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.Collection;
import java.util.List;

/**
 * Excel导出处理
 *
 * @author jianchen
 * @version 1.0.0
 * @date 2024/8/8
 */
@Slf4j
public class ExcelExportHandler {
    /** Excel sheet最大行数，默认65536 */
    public static final int MAX_SHEET_LINE = 65536;

    public static final String FORMULA_REGEX_STR = "=|-|\\+|@";
    public static final String[] FORMULA_STR = {"=", "-", "+", "@"};

    /**
     * 导出处理
     * @param clazz 导出entity类型
     * @param list 导出数据列表
     * @param response Servlet响应
     * @param sheetName sheet名称
     * @param title 表格标题
     */
    public static <T> void handle(Class<T> clazz,
                                  List<T> list,
                                  HttpServletResponse response,
                                  String sheetName,
                                  String title) {
        ExcelBean<T> excelBean = new ExcelBean<>(clazz);
        ExcelBeanInitializer.getInstance(excelBean).exportInit(list, sheetName, title);
        try {
            addSheet(excelBean);
            excelBean.getWb().write(response.getOutputStream());
        } catch (Exception e) {
            log.error("导出Excel异常{}", e.getMessage());
        } finally {
            IOUtils.closeQuietly(excelBean.getWb());
        }
    }

    /**
     * 导出处理
     *
     * @param clazz 导出entity类型
     * @param list 导出数据列表
     * @param filename  文件全路径名称
     * @param sheetName sheet名称
     * @param title 表格标题
     */
    public static <T> void handle(Class<T> clazz,
                                  List<T> list,
                                  String filename,
                                  String sheetName,
                                  String title) {
        ExcelBean<T> excelBean = new ExcelBean<>(clazz);
        ExcelBeanInitializer.getInstance(excelBean).exportInit(list, sheetName, title);
        OutputStream out = null;
        try {
            addSheet(excelBean);
            out = new FileOutputStream(filename);
            excelBean.getWb().write(out);
        } catch (Exception e) {
            log.error("导出Excel异常{}", e.getMessage());
            throw new ServiceException("导出Excel失败，请联系网站管理员！");
        } finally {
            IOUtils.closeQuietly(excelBean.getWb());
            IOUtils.closeQuietly(out);
        }
    }

    /**
     * 创建Sheet，写入数据
     */
    private static void addSheet(ExcelBean<?> bean) {
        // 取出一共有多少个sheet.
        int sheetNo = Math.max(1, (int) Math.ceil(bean.getList().size() * 1.0 / MAX_SHEET_LINE));
        for (int index = 0; index < sheetNo; index++) {
            bean.createSheet(sheetNo, index);

            // 产生一行
            Row row = bean.getSheet().createRow(bean.getRowNum());
            int column = 0;
            // 创建标题行
            for (Object[] os : bean.getFields()) {
                Field field = (Field) os[0];
                Excel excel = (Excel) os[1];
                if (Collection.class.isAssignableFrom(field.getType())) {
                    for (Field subField : bean.getSubFields()) {
                        Excel subExcel = subField.getAnnotation(Excel.class);
                        bean.createHeadCell(subExcel, row, column++);
                    }
                } else {
                    bean.createHeadCell(excel, row, column++);
                }
            }
            if (ExcelType.EXPORT.equals(bean.getType())) {
                fillDataToExcel(bean, index);
                bean.createStatRow();
            }
        }
    }

    /**
     * 向excel填充数据
     * @param index 序号
     */
    private static <T> void fillDataToExcel(ExcelBean<T> excelBean, int index) {
        int startNo = index * MAX_SHEET_LINE;
        int endNo = Math.min(startNo + MAX_SHEET_LINE, excelBean.getList().size());
        int rowNo = (1 + excelBean.getRowNum()) - startNo;
        for (int i = startNo; i < endNo; i++) {
            rowNo = excelBean.hasSubList() ? (i > 1 ? rowNo + 1 : rowNo + i) : i + 1 + excelBean.getRowNum() - startNo;
            Row row = excelBean.getSheet().createRow(rowNo);
            // 得到导出对象.
            T vo = excelBean.getList().get(i);
            Collection<?> subList = null;
            if (excelBean.hasSubList(vo)) {
                if (excelBean.hasSubList(vo)) {
                    subList = excelBean.getSubListValue(vo);
                    excelBean.setSubMergedLastRowNum(excelBean.getSubMergedLastRowNum() + subList.size());
                } else {
                    excelBean.setSubMergedFirstRowNum(excelBean.getSubMergedFirstRowNum()+1);
                    excelBean.setSubMergedLastRowNum(excelBean.getSubMergedLastRowNum()+1);
                }
            }

            int column = 0;
            for (Object[] os : excelBean.getFields()) {
                Field field = (Field) os[0];
                Excel excel = (Excel) os[1];
                if (Collection.class.isAssignableFrom(field.getType()) && subList != null) {
                    boolean subFirst = false;
                    for (Object obj : subList) {
                        if (subFirst) {
                            rowNo++;
                            row = excelBean.getSheet().createRow(rowNo);
                        }
                        List<Field> subFields = FieldUtils.getFieldsListWithAnnotation(obj.getClass(), Excel.class);
                        int subIndex = 0;
                        for (Field subField : subFields) {
                            if (subField.isAnnotationPresent(Excel.class)) {
                                subField.setAccessible(true);
                                Excel attr = subField.getAnnotation(Excel.class);
                                addCell(excelBean, attr, row, obj, subField, column + subIndex);
                            }
                            subIndex++;
                        }
                        subFirst = true;
                    }
                    excelBean.setSubMergedLastRowNum(excelBean.getSubMergedLastRowNum() + subList.size());
                } else {
                    addCell(excelBean, excel, row, vo, field, column++);
                }
            }
        }
    }

    /**
     * 添加单元格
     */
    public static <T> void addCell(ExcelBean<T> excelBean,
                                   Excel attr,
                                   Row row,
                                   Object obj,
                                   Field field,
                                   int column) {
        Cell cell;
        try {
            // 设置行高
            row.setHeight(excelBean.getMaxHeight());
            // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
            if (attr.isExport()) {
                // 创建cell
                cell = row.createCell(column);
                if (excelBean.hasSubList(obj) && excelBean.getSubListValue(obj).size() > 1 && attr.needMerge()) {
                    CellRangeAddress cellAddress = new CellRangeAddress(excelBean.getSubMergedFirstRowNum(), excelBean.getSubMergedLastRowNum(), column, column);
                    excelBean.getSheet().addMergedRegion(cellAddress);
                }
                String key = StrUtil.format("data_{}_{}_{}", attr.align(), attr.color(), attr.backgroundColor());
                cell.setCellStyle(excelBean.getCellStyle(key));

                // 用于读取对象中的属性
                Object value = getTargetValue(obj, field, attr);
                String dateFormat = attr.dateFormat();
                String readConverterExp = attr.readConverterExp();
                String separator = attr.separator();
                if (value != null && field.getType().isEnum()) {
                    // 枚举字段特殊处理
                    if (!BaseEnum.class.isAssignableFrom(field.getType())) {
                        throw new ServiceException(field.getName() + "必须是BaseEnum的子类");
                    }
                    BaseEnum baseEnum = (BaseEnum) value;
                    cell.setCellValue(baseEnum.getLabel());
                } else if (StrUtil.isNotEmpty(dateFormat) && value != null) {
                    cell.setCellValue(ExcelFieldHandler.format(dateFormat, value));
                } else if (StrUtil.isNotEmpty(readConverterExp) && value != null) {
                    cell.setCellValue(ExcelFieldHandler.convertByExp(Convert.toStr(value), readConverterExp, separator));
                } else if (value instanceof BigDecimal && -1 != attr.scale()) {
                    cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).doubleValue());
                } else if (!attr.formatter().equals(ExcelFormatter.class)) {
                    cell.setCellValue(Convert.toStr(ExcelFieldHandler.customFormat(value, attr.formatter(), attr.args())));
                } else {
                    // 设置单元格值
                    setCellValue(value, attr, cell);
                }
                excelBean.statData(column, Convert.toStr(value), attr.isStatistics());
            }
        } catch (Exception e) {
            log.error("导出Excel失败", e);
        }
    }

    /**
     * 获取bean中的属性值
     *
     * @param vo    实体对象
     * @param field 字段
     * @param excel 注解
     * @return 最终的属性值
     */
    private static <T> Object getTargetValue(T vo, Field field, Excel excel) throws Exception {
        Object obj = field.get(vo);
        if (StrUtil.isNotEmpty(excel.targetAttr())) {
            String target = excel.targetAttr();
            if (target.contains(".")) {
                String[] targets = target.split("[.]");
                for (String name : targets) {
                    obj = ReflectUtil.getFieldValue(obj, name);
                }
            } else {
                obj = ReflectUtil.getFieldValue(obj, target);
            }
        }
        return obj;
    }

    /**
     * 设置单元格值
     *
     * @param value 单元格值
     * @param attr  注解相关
     * @param cell  单元格信息
     */
    public static void setCellValue(Object value, Excel attr, Cell cell) {
        if (ColumnType.STRING == attr.cellType()) {
            String cellValue = Convert.toStr(value);
            // 对于任何以表达式触发字符 =-+@开头的单元格，直接使用tab字符作为前缀，防止CSV注入。
            if (StrUtil.startWithAny(cellValue, FORMULA_STR)) {
                cellValue = RegExUtils.replaceFirst(cellValue, FORMULA_REGEX_STR, "\t$0");
            }
            if (value instanceof Collection && StrUtil.equals("[]", cellValue)) {
                cellValue = StrUtil.EMPTY;
            }
            cell.setCellValue(cellValue == null ? attr.defaultValue() : cellValue + attr.suffix());
        } else if (ColumnType.NUMERIC == attr.cellType()) {
            if (value != null) {
                cell.setCellValue(StrUtil.contains(Convert.toStr(value), ".") ? Convert.toDouble(value) : Convert.toInt(value));
            }
        } else if (ColumnType.IMAGE == attr.cellType()) {
            ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1);
            String imagePath = Convert.toStr(value);
            if (StrUtil.isNotEmpty(imagePath)) {
                byte[] data = ImageUtil.getImage(imagePath);
                int imageType = PoiUtil.getImageType(data);
                PoiUtil.getDrawingPatriarch(cell.getSheet())
                        .createPicture(anchor, cell.getSheet().getWorkbook().addPicture(data, imageType));
            }
        }
    }
}